﻿Imports System.IO
Imports System.Data.SqlClient


Public Class Form1
    Public Class LoginInformation
        Public username As String
        Public email As String
        Public company As String
        Public department As String
        Public role As String
        Public password As String
    End Class
    Private Sub Form1_DragDrop(sender As Object, e As DragEventArgs) Handles MyBase.DragDrop

    End Sub
    Public settingsdialog As New settings
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        CheckForIllegalCrossThreadCalls = False
        Me.AllowDrop = True
        'FilePath.AllowDrop = True
        AddHandler Me.DragEnter, AddressOf Main_DragEnter
        AddHandler ItemPanel.DragEnter, AddressOf Main_DragEnter
        settingsdialog.Mainform = Me
        loginform.mainForm = Me
        Users.MainForm = Me

        ' My.Settings.Reload()
        li.username = My.Settings.username
        li.password = My.Settings.password
        li.email = My.Settings.email
        li.company = My.Settings.company
        li.department = My.Settings.department
        li.role = My.Settings.role
        settingsdialog.txtroot.Text = My.Settings.rootdir
        My.Settings.Reload()

        If li.username = "" Then

        Else
            If li.role = "Admin" Then
                AdminToolsToolStripMenuItem.Visible = True
            End If
            LoginToolStripMenuItem.Visible = False

        End If
        LoadITems()
    End Sub
    Private Sub Main_DragDrop(ByVal sender As Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles Me.DragDrop, ItemPanel.DragDrop
        If e.Data.GetDataPresent("FileDrop", True) = True Then

            Dim Files() As String
            Dim i As Integer

            Files = e.Data.GetData(DataFormats.FileDrop)
            Dim color1 As Integer = 1

            For i = 0 To Files.Length - 1
                'FileList.Items.Add(Files(i))
                Dim newitem As New additem
                Dim info As New FileInfo(Files(i))
                If info.Extension <> Nothing And info.Extension = ".pdf" Then
                    For Each ItemPanel As additem In Me.ItemPanel.Controls
                        If ItemPanel.FileInformation.Name = info.Name Then
                            GoTo nextitem
                        End If
                    Next
                    newitem.mainform = Me
                    newitem.FileInformation = info
                    'Load vendor
                    If color1 = 1 Then
                        color1 = 0
                        newitem.BackColor = Color.WhiteSmoke
                    ElseIf color1 = 0 Then
                        color1 = 1
                        newitem.BackColor = Color.Gainsboro
                    End If
                    ItemPanel.Controls.Add(newitem)
                End If
nextitem:
            Next

        End If
    End Sub


    Private Sub Main_DragEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles Me.DragEnter
        If e.Data.GetDataPresent(DataFormats.FileDrop) Then
            e.Effect = DragDropEffects.All
        End If
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        For Each ItemPanel As additem In Me.ItemPanel.Controls
            ItemPanel.include.Checked = True
        Next
    End Sub

    Private Sub SettingsToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SettingsToolStripMenuItem.Click
        settingsdialog.ShowDialog()
    End Sub
    Public loginform As New LoginForm1
    Dim li As New LoginInformation
    Private Sub LoginToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles LoginToolStripMenuItem.Click
        ShowLogin()
    End Sub
    Dim Users As New adduser
    Private Sub AddUsersToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles AddUsersToolStripMenuItem.Click
        Users.ShowDialog()
    End Sub
    Sub ShowLogin()
        loginform.loggedon = False
        loginform.loggedon = False
        loginform.ShowDialog()
        If loginform.loggedon = True Then
            li = loginform.logininfo

            'AdminToolsToolStripMenuItem
            If li.role = "Admin" Then
                AdminToolsToolStripMenuItem.Visible = True
            End If
            LoginToolStripMenuItem.Visible = False
        End If



    End Sub
    Private Sub ReLoginToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ReLoginToolStripMenuItem.Click
        ShowLogin()
    End Sub

    Private Sub VendorBindingSource_CurrentChanged(sender As Object, e As EventArgs)

    End Sub

    Dim CopyThreadCounter As Integer = 0
    Dim CopyThreadCount As Integer = 0
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        For Each ItemPanel As additem In Me.ItemPanel.Controls
            If (ItemPanel.include.Checked = True) Then
                CopyThreadCount += 1
            End If
        Next
        Dim Copythreadlist As New ArrayList
        CopyThreadCounter = 0
        CopyThreadCount = 0
        For Each ItemPanel As additem In Me.ItemPanel.Controls
            If (ItemPanel.include.Checked = True) Then
                'copy
                Dim a As New Threading.Thread(AddressOf filecopy)
                a.Start(ItemPanel)
                Copythreadlist.Add(a)
            End If
        Next


    End Sub
    Function CompareOriginalFileGromNew(OriginalFilenamefromlocal As String, filenameOnUserControl As String) As Boolean
        Dim con As New SqlConnection
        Dim reader As SqlDataReader

        con.ConnectionString = settingsdialog.txtconstr.Text
        Dim cmd As New SqlCommand("SELECT * FROM [DocumentManagement].[dbo].[records] WHERE originalfilename='" & OriginalFilenamefromlocal & "' AND serverfilename='" & filenameOnUserControl & "'", con)
        'MsgBox("SELECT * FROM [DocumentManagement].[dbo].[files] WHERE originalfilename='" & OriginalFilenamefromlocal & "' AND filename='" & filenameOnUserControl & "'")
        ' InputBox("", "", "SELECT * FROM [DocumentManagement].[dbo].[files] WHERE originalfilename='" & OriginalFilenamefromlocal & "' AND filename='" & filenameOnUserControl & "'")

        con.Open()
        reader = cmd.ExecuteReader()
        While reader.Read()
            If reader(6) <> Nothing Then
                Return False
            End If
        End While
        con.Close()
        Return True
    End Function
    Sub filecopy(usercntrl As additem)
        CopyThreadCounter += 1
        Dim Root As String
        Root = settingsdialog.txtroot.Text

        Dim ServerLocation As String = Root & li.department & "\" & usercntrl.txttitle.Text
        Dim company, owner, vendor, title, docutype, dateissue, originalfilename, serverfilename, description1, description2, description3 As String
        company = li.company.ToLower
        owner = li.username.ToLower
        vendor = usercntrl.selectvendor.Text.ToLower
        title = usercntrl.txttitle.Text.ToLower
        docutype = usercntrl.ComboBox1.Text
        dateissue = usercntrl.DateTimePicker1.Value.ToString.ToLower
        originalfilename = usercntrl.FileInformation.FullName
        serverfilename = Root & li.department & "\" & usercntrl.FileInformation.Name

        'Insert New Item
        'Update
        'Insert in New Filename
        Dim rand As New Random
        Dim Random As Integer = rand.Next(100, 999)
        Dim sqlcommand As String = ""
        If File.Exists(serverfilename) Then
            'check if different file
            If CompareOriginalFileGromNew(usercntrl.FileInformation.FullName, serverfilename) = False Then
                'update only
                sqlcommand = "UPDATE [DocumentManagement].[dbo].[records] SET [vendor]='" & vendor & "',[title]='" & title & "',[docutype]='" & docutype & "',[dateissue]='" & dateissue & "' WHERE [originalfilename]='" & originalfilename & "' AND serverfilename='" & serverfilename & "' AND owner='" & owner & "'"
            Else
                'insert but add randomnumber
                serverfilename = Root & li.department & "\" & Random.ToString & usercntrl.FileInformation.Name
                ServerLocation = serverfilename
                sqlcommand = "INSERT INTO [DocumentManagement].[dbo].[records] ([company],[owner],[vendor],[title],[docutype],[dateissue],[originalfilename],[serverfilename],[description1],[description2],[description3]) VALUES ('" & company & "','" & owner & "','" & vendor & "','" & title & "','" & docutype & "','" & dateissue & "','" & originalfilename & "','" & serverfilename & "','" & description1 & "','" & description2 & "','" & description3 & "')"
            End If
        Else
            'insert new item
            sqlcommand = "INSERT INTO [DocumentManagement].[dbo].[records] ([company],[owner],[vendor],[title],[docutype],[dateissue],[originalfilename],[serverfilename],[description1],[description2],[description3]) VALUES ('" & company & "','" & owner & "','" & vendor & "','" & title & "','" & docutype & "','" & dateissue & "','" & originalfilename & "','" & serverfilename & "','" & description1 & "','" & description2 & "','" & description3 & "')"
        End If

        Dim con As New SqlConnection
        con.ConnectionString = settingsdialog.txtconstr.Text
        Dim cmd As New SqlCommand(sqlcommand, con)
        Try
            con.Open()
            cmd.ExecuteNonQuery()
            usercntrl.lblstatus.Text = "Success"
            con.Close()
        Catch ex As Exception
            usercntrl.include.Checked = False
            usercntrl.lblstatus.Text = ex.Message
        End Try


        File.Copy(usercntrl.FileInformation.FullName, ServerLocation, True)

        If (CopyThreadCounter >= CopyThreadCount) Then
            'MsgBox("Done")
            SyncLock Me
                Threading.Thread.Sleep(100)
                RemoveDoneItems()
            End SyncLock
            ToolStripStatusLabel1.Text = "Done"
            LoadItems()
        End If
    End Sub
    Sub RemoveDoneItems()
        For Each ItemPanel As additem In Me.ItemPanel.Controls
            If (ItemPanel.include.Checked = True) Then
                Try
                    ItemPanel.Dispose()
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End If
        Next
    End Sub

    Private Sub ItemPanel_DoubleClick(sender As Object, e As EventArgs) Handles ItemPanel.DoubleClick
        Dim openfiledialog1 As New OpenFileDialog
        openfiledialog1.Title = "PDF Files"
        openfiledialog1.Multiselect = True
        openfiledialog1.Filter = "PDF Files (*.pdf) |*.pdf"
        If openfiledialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Dim color1 As Integer = 1

            For Each items In openfiledialog1.FileNames
                Dim info As New FileInfo(items)
                Dim newitem As New additem
                For Each ItemPanel As additem In Me.ItemPanel.Controls
                    If ItemPanel.FileInformation.Name = info.Name Then
                        GoTo nextitem
                    End If
                Next
                newitem.mainform = Me
                newitem.FileInformation = info
                'load vendor
                If color1 = 1 Then
                    color1 = 0
                    newitem.BackColor = Color.WhiteSmoke
                ElseIf color1 = 0 Then
                    color1 = 1
                    newitem.BackColor = Color.Gainsboro
                End If
                ItemPanel.Controls.Add(newitem)
nextitem:
            Next

        End If
    End Sub

    Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
        LoadItems(txtquery.Text)
    End Sub

    Sub LoadItems(Optional query As String = "")

        Try
            viewpanel.Controls.Clear()

            Dim SQLCommand As String
            If query = "" And categorylist.SelectedIndex = -1 Then
                SQLCommand = "Select * FROM [DocumentManagement].[dbo].[records] where company = '" & li.company & "' and owner='" & li.username & "'"
            ElseIf query <> "" And categorylist.SelectedIndex = -1 Then
                SQLCommand = "Select * FROM [DocumentManagement].[dbo].[records] WHERE [title] LIKE '%" & query & "%' OR [vendor] LIKE '%" & query & "%' OR [docutype] LIKE '%" & query & "%' OR [dateissue] LIKE '%" & query & "%' OR [originalfilename] LIKE '%" & query & "%' AND company = '" & li.company & "' and owner='" & li.username & "'"
                'company = '" & li.company & "' and owner='" & li.username & "'"
            Else
                If categorylist.SelectedIndex = 0 Then
                    SQLCommand = "Select * FROM [DocumentManagement].[dbo].[records] where company = '" & li.company & "' and owner='" & li.username & "' and title like '%" & query & "%'"
                ElseIf categorylist.SelectedIndex = 1 Then
                    SQLCommand = "Select * FROM [DocumentManagement].[dbo].[records] where company = '" & li.company & "' and owner='" & li.username & "' and [docutype] like '%" & query & "%'"
                ElseIf categorylist.SelectedIndex = 2 Then
                    SQLCommand = "Select * FROM [DocumentManagement].[dbo].[records] where company = '" & li.company & "' and owner='" & li.username & "' and [dateissue] like '%" & query & "%'"
                ElseIf categorylist.SelectedIndex = 3 Then
                    SQLCommand = "Select * FROM [DocumentManagement].[dbo].[records] where company = '" & li.company & "' and owner='" & li.username & "' and [originalfilename] like '%" & query & "%'"
                ElseIf categorylist.SelectedIndex = 4 Then
                    SQLCommand = "Select * FROM [DocumentManagement].[dbo].[records] where company = '" & li.company & "' and owner='" & li.username & "' and [vendor] like '%" & query & "%'"
                End If
            End If



            Dim con As New SqlConnection
            Dim reader As SqlDataReader

            con.ConnectionString = settingsdialog.txtconstr.Text
            Dim cmd As New SqlCommand(SQLCommand, con)
            con.Open()

            reader = cmd.ExecuteReader()
            Dim color1 As Integer = 1
            While reader.Read()
                'Console.WriteLine(String.Format("{0}, {1}", reader(0), reader(1)))
                Dim newitem As New viewitem
                newitem.MainForm = Me
                Dim info As New FileInfo(reader(8))
                newitem.ServerFile = info
                newitem.Upid = reader(0)
                newitem.lbltitle.Text = reader(4)
                newitem.lblinfo.Text = reader(3) & " • " & reader(5) & " • " & reader(6) & " • " & Math.Round(info.Length / 1024) & "KB"
                'color
                If color1 = 1 Then
                    color1 = 0
                    newitem.BackColor = Color.Gainsboro
                ElseIf color1 = 0 Then
                    color1 = 1
                    newitem.BackColor = Color.WhiteSmoke
                End If
                viewpanel.Controls.Add(newitem)
            End While
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical)
        End Try
    End Sub

   
    Private Sub txtquery_TextChanged(sender As Object, e As EventArgs) Handles txtquery.TextChanged
       
    End Sub

    Private Sub categorylist_SelectedIndexChanged(sender As Object, e As EventArgs) Handles categorylist.SelectedIndexChanged

    End Sub

    Private Sub txtquery_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtquery.KeyPress
        If e.KeyChar = Convert.ToChar(Keys.Enter) Then
            LoadItems(txtquery.Text)
        End If
    End Sub


End Class
